import jaydebeapi
import pandas as pd
from gavial_scriptsdk.Constants import *


def read_jdbc(sql, jclassname, url, driver_args, jars=None, libs=None):
  """
  Reads jdbc compliant data sources and returns a Pandas DataFrame

  uses jaydebeapi.connect and doc strings :-)
  https://pypi.python.org/pypi/JayDeBeApi/

  :param sql: select statement
  :param jclassname: Full qualified Java class name of the JDBC driver.
      e.g. org.postgresql.Driver or org.h2.Driver
  :param url: jdbc url. e.g. jdbc:h2:tcp://localhost:8082/mem:test
  :param driver_args: Argument or sequence of arguments to be passed to the
     Java DriverManager.getConnection method. Usually the
     database URL. See
     http://docs.oracle.com/javase/6/docs/api/java/sql/DriverManager.html
     for more details
  :param jars: Jar filename or sequence of filenames for the JDBC driver
  :param libs: Dll/so filenames or sequence of dlls/sos used as
     shared library by the JDBC driver
  :return: Pandas DataFrame
  """

  try:
    conn = jaydebeapi.connect(jclassname, url, driver_args, jars, libs)
    curs = conn.cursor()
    curs.execute(sql)
    # getting column headers
    columns = [desc[0] for desc in curs.description]
    # convert the list of tuples from fetchall() to a df
    return pd.DataFrame(curs.fetchall(), columns=columns)
  except jaydebeapi.DatabaseError as de:
    raise
  finally:
    curs.close()
    conn.close()


def write_jdbc(df, table, jclassname, url, driver_args, jars=None, libs=None):
  """
  save Pandas DataFrame to db by jdbc

  uses jaydebeapi.connect and doc strings :-)
  https://pypi.python.org/pypi/JayDeBeApi/
  :param df: Pandas DataFrame.
  :param table: table name that the Pandas DataFrame would be saved to.
  :param jclassname: Full qualified Java class name of the JDBC driver.
      e.g. org.postgresql.Driver or org.h2.Driver
  :param url: jdbc url. e.g. jdbc:h2:tcp://localhost:8082/mem:test
  :param driver_args: Argument or sequence of arguments to be passed to the
     Java DriverManager.getConnection method. Usually the
     database URL. See
     http://docs.oracle.com/javase/6/docs/api/java/sql/DriverManager.html
     for more details
  :param jars: Jar filename or sequence of filenames for the JDBC driver
  :param libs: Dll/so filenames or sequence of dlls/sos used as
     shared library by the JDBC driver
  :return: None
  """

  try:
    conn = jaydebeapi.connect(jclassname, url, driver_args, jars, libs)

  except jaydebeapi.DatabaseError as de:
    raise
  finally:
    conn.close()


def test():
  # sql = "select * from db1.test limit 10"
  """
  sql = "select * from schema_aceac38edddc48348b3e3fb42e7c0e49.variables"
  driver_class = "org.h2.Driver"
  url = "jdbc:h2:tcp://localhost:8083/mem:test"
  driver_args = ["test","123456"]
  jar = "./jar/h2-1.4.199.jar"
  df = read_jdbc(sql, driver_class, url, driver_args, jar)
  print(df)
  """
  # print(df.sort_values(by='AREA_CODE', ascending=False))
  # print(df)
  # write_jdbc(df, 'my_test', driver_class, url, driver_args, jar)
  print(STORAGE_TYPE)
  url = "jdbc:h2:tcp://localhost:%s/mem:%s" % (DB_PORT, DB)
  print(url)


if __name__ == "__main__":
  test()
